本來還傻傻的想用2個迴圈下去做,後來看到有人用INNER JOIN 做,這作法,真聰明!!
--找出這批ng帽子,生產前後1分鐘的批號.

DROP TABLE #TMP1--這個資料表放NG的帽子
create table  #TMP1
(
	CODE VARCHAR(10) 
	,TAG VARCHAR(30)
	,產品 varchar(30) 
	,EDATE DATE
	,TIME1 datetime 
)
DROP TABLE #TMP2--這個資料表放非這批的帽子
create table  #TMP2
(
	CODE VARCHAR(10) 
	,TAG VARCHAR(30)
	,產品 varchar(30) 	
	,EDATE DATE
	,TIME1 datetime 
) 
DECLARE @SECONDS INT =60;
TRUNCATE table #TMP1
TRUNCATE table #TMP2
DECLARE @DATE DATETIME =getdate()
insert into #TMP1 select 1,'NG','帽子' ,@DATE,@DATE 
insert into #TMP2 select 2,'','帽子' ,@DATE,DATEADD (SECOND,30, @DATE) 
insert into #TMP2 select 3,'','帽子' ,@DATE,DATEADD (MINUTE,1,  @DATE) 
insert into #TMP2 select 3,'','帽子' ,@DATE,DATEADD (MINUTE,-1,  @DATE)
insert into #TMP2 select 4,'','帽子' ,@DATE,DATEADD (MINUTE,2, @DATE) 
insert into #TMP2 select 5,'','手套' ,@DATE,DATEADD (SECOND,30, @DATE) 
 

SELECT ABS(DATEDIFF(SS,A.TIME1,B.TIME1)) ,A.*,B.*
		  FROM #TMP1 A,#TMP2 B 
		 WHERE A.EDATE = B.EDATE
		   AND A.產品 = B.產品 
		   AND A.CODE != B.CODE
		   AND ABS(DATEDIFF(SS,A.TIME1,B.TIME1)) <= @SECONDS